################################ IMPORTS ###############################

import MySQLdb
import _mysql_exceptions

from math import exp, pi
from scipy.integrate import quad

################################ GET DATA FROM DATABASE ###############################

### connect to the hunter SQL database
mysql_host = '112.124.1.3'
mysql_port = 3306
mysql_user = 'hunter_guest'
mysql_passwd = 'hunter_guest'
mysql_db = 'hunter'

conn = MySQLdb.connect(host=mysql_host, port=mysql_port,
	user=mysql_user, passwd=mysql_passwd, db=mysql_db, charset='utf8')
cursor = conn.cursor()

### get product_id and category from the product table
stmt='SELECT product_id, category FROM product'
cursor.execute(stmt)
results_product=cursor.fetchall()

### get product_id, score, and category from the score_p table
stmt='SELECT product_id, score, category FROM score_p'
cursor.execute(stmt)
results_score_p=cursor.fetchall()

### get all product_id, feature names, feature scores, and category from the score_pf table
stmt='SELECT product_id, feature, score, category FROM score_pf'
cursor.execute(stmt)
results_score_pf=cursor.fetchall()

### Get all categories
stmt="SELECT DISTINCT category FROM product"
cursor.execute(stmt)
results=cursor.fetchall()
categories=[]
for result in results:
	for x in result:
		categories.append(x)

### get all product_id listed in product table
stmt='SELECT product_id FROM product'
cursor.execute(stmt)
results=cursor.fetchall()
id_list=[]
for result in results:
	for x in result:
		id_list.append(x)
id_list=set(id_list)
id_list=list(id_list)

### get all feature names and their categories listed in the score_pf table
stmt='SELECT feature, category FROM score_pf'
cursor.execute(stmt)
feature_list=cursor.fetchall()
feature_list=set(feature_list)
feature_list=list(feature_list)

### Close the connection
cursor.close()
conn.close()

################################ CALCULATE PERCENTILES ###############################

### make function that gets the mean of a list of numbers
def mean(list):
	n=len(list)
	count=0
	for x in xrange(0,n):
		count+=float(list[x])
	return (float(count)/float(n))

### make function that gets the sd of a list of numbers
def sd(list):
	n=len(list)
	SS=0
	for x in xrange(0,n):
		SS+=(float(list[x])-float(mean(list)))**2
	return (float(SS)/float((n-1)))**.5

### calculate average scores and sd for each category
averages_p=[]
for category in categories:
	values_vec=[]
	for result in results_score_p:
		if result[2]==category:
			values_vec.append(result[1])
	try:
		mu=mean(values_vec)
	except ZeroDivisionError:
		mu=0
	data_vec=[]
	data_vec.append(mu)
	data_vec.append(category)
	averages_p.append(data_vec)

standard_devs_p=[]
for category in categories:
	values_vec=[]
	for result in results_score_p:
		if result[2]==category:
			values_vec.append(result[1])
	sigma=sd(values_vec)
	data_vec=[]
	data_vec.append(sigma)
	data_vec.append(category)
	standard_devs_p.append(data_vec)
	
"""### calculate percentile of each product score
percentiles_p=[]
for result in results_score_p:
	id=result[0]
	p_score=result[1]			
	category=result[2]
	for value in averages_p:
		if value[0]==category:
			c_mean=value[1]
	for value in standard_devs_p:
		if value[0]==category:
			c_sd=value[1]
	try: 
		z_value=(float(p_score)-float(c_mean))/float(c_sd)
		I=quad(normal, -100, z_value)
		percentile=I[0]
	except ZeroDivisionError:
		percentile=None
	data_vec=[]
	data_vec.append(id)
	data_vec.append(percentile)
	data_vec.append(category)
	percentiles_p.append(data_vec)
"""

### calculate average feature scores and sd for each category
averages_pf=[]
for category in categories:
	for feature in feature_list:
		if feature[1]==category:
			feature_name=feature[0]
			values_vec=[]
			for result in results_score_pf:
				if result[3]==category and result[1]==feature_name:
					values_vec.append(result[2])
			try:
				mu=mean(values_vec)
			except ZeroDivisionError:
				mu=0
			data_vec=[]	
			data_vec.append(feature_name)
			data_vec.append(mu)
			data_vec.append(category)
			averages_pf.append(data_vec)

standard_devs_pf=[]
for category in categories:
	for feature in feature_list:
		if feature[1]==category:
			feature_name=feature[0]			
			values_vec=[]
			for result in results_score_pf:
				if result[3]==category and result[1]==feature_name:
					values_vec.append(result[2])
			sigma=sd(values_vec)
			data_vec=[]
			data_vec.append(feature_name)
			data_vec.append(sigma)
			data_vec.append(category)
			standard_devs_pf.append(data_vec)

"""### calculate percentile of each feature score
percentiles_pf=[]
for result in results_score_pf:
	id=result[0]
	feature_name=result[1]
	pf_score=result[2]
	category=result[3]
	for value in averages_pf:
		if value[0]==category and value[1]==feature_name:
			cf_mean=value[2]
	for value in standard_devs_pf:
		if value[0]==category and value[1]==feature_name:
			cf_sd=value[2]
	try: 
		z_value=(float(pf_score)-float(cf_mean))/float(cf_sd)
		I=quad(normal, -100, z_value)
		percentile=I[0]
	except ZeroDivisionError:
		percentile=None				
	data_vec=[]
	data_vec.append(id)
	data_vec.append(feature_name)
	data_vec.append(percentile)
	data_vec.append(category)
	percentiles_pf.append(data_vec)
"""
				
################################ CREATE NEW AVERAGE AND SD TABLES ###############################

### connect to the hunter SQL database
mysql_host = '112.124.1.3'
mysql_port = 3306
mysql_user = 'hunter_guest'
mysql_passwd = 'hunter_guest'
mysql_db = 'hunter'

conn = MySQLdb.connect(host=mysql_host, port=mysql_port,
	user=mysql_user, passwd=mysql_passwd, db=mysql_db, charset='utf8')
cursor = conn.cursor()

### create average_score_p table
try:
	cursor.execute('DROP TABLE average_score_p')
except _mysql_exceptions.OperationalError:
	pass
cursor.execute('CREATE TABLE average_score_p(id INT AUTO_INCREMENT PRIMARY KEY, average_score DOUBLE, category VARCHAR(200))')

### create sd_score_p table
try:
	cursor.execute('DROP TABLE sd_score_p')
except _mysql_exceptions.OperationalError:
	pass
cursor.execute('CREATE TABLE sd_score_p(id INT AUTO_INCREMENT PRIMARY KEY, sd_score DOUBLE, category VARCHAR(200))')

### create average_score_pf table
try:
	cursor.execute('DROP TABLE average_score_pf')
except _mysql_exceptions.OperationalError:
	pass
cursor.execute('CREATE TABLE average_score_pf(id INT AUTO_INCREMENT PRIMARY KEY, feature VARCHAR(200), average_score DOUBLE, category VARCHAR(200))')

### create sd_score_pf table
try:
	cursor.execute('DROP TABLE sd_score_pf')
except _mysql_exceptions.OperationalError:
	pass
cursor.execute('CREATE TABLE sd_score_pf(id INT AUTO_INCREMENT PRIMARY KEY, feature VARCHAR(200), sd_score DOUBLE, category VARCHAR(200))')

################################ POPULATE NEW TABLES WITH DATA ###############################

### Populate average_score_p
total=len(averages_p)
count=0
for result in averages_p:
	count+=1
	percentage=round(100*float(count)/float(total),2)
	average_score=result[0]
	category=result[1]
	new_entry=(average_score, category)
	stmt=("INSERT INTO average_score_p (average_score,category) VALUES (%s,%s)")	
	cursor.execute(stmt, new_entry)
	conn.commit()
	print 'average_score_p: %.2f%% done' % percentage

### Populate sd_score_p
total=len(standard_devs_p)
count=0
for result in standard_devs_p:
	count+=1
	percentage=round(100*float(count)/float(total),2)
	sd_score=result[0]
	category=result[1]
	new_entry=(sd_score, category)
	stmt=("INSERT INTO sd_score_p (sd_score,category) VALUES (%s,%s)")	
	cursor.execute(stmt, new_entry)
	conn.commit()
	print 'sd_score_p: %.2f%% done' % percentage

### Populate average_score_pf
total=len(averages_pf)
count=0
for result in averages_pf:
	count+=1
	percentage=round(100*float(count)/float(total),2)
	feature=result[0]
	average_score=result[1]
	category=result[2]
	new_entry=(feature,average_score, category)
	stmt=("INSERT INTO average_score_pf (feature,average_score,category) VALUES (%s,%s,%s)")	
	cursor.execute(stmt, new_entry)
	conn.commit()
	print 'average_score_pf: %.2f%% done' % percentage

### Populate sd_score_pf
total=len(standard_devs_pf)
count=0
for result in standard_devs_pf:
	count+=1
	percentage=round(100*float(count)/float(total),2)
	feature=result[0]
	sd_score=result[1]
	category=result[2]
	new_entry=(feature, sd_score, category)
	stmt=("INSERT INTO sd_score_pf (feature,sd_score,category) VALUES (%s,%s,%s)")	
	cursor.execute(stmt, new_entry)
	conn.commit()
	print 'sd_score_pf: %.2f%% done' % percentage

### Close the connection
cursor.close()
conn.close()
